# Title     : TODO
# Objective : TODO
# Created by: xueyj
# Created on: 2020/11/20
options(warn = -1)
rm(list = ls(all = TRUE))

pacman::p_load(RMySQL, dplyr, magrittr, lazyopt)


load("D:/project/R-use/Rmysql/rm-f.RData")

criterions_cite <- function(dbname, username, password, host, port, sql) {
  #建立连接，project_name表示项目数据库名称
  conn <- dbConnect(MySQL(), dbname = dbname, username = username, password = password, host = host, port = port)
  #如果表中有中文出现乱码，可以添加以下代码
  dbSendQuery(conn, 'SET NAMES GBK')
  #过滤数据
  res <- dbSendQuery(conn, sql)
  #提取数据，-1表示全部提取，3表示取三行数据
  dat <- dbFetch(res, -1)
  #关闭RMySQL中的数据集
  dbClearResult(dbListResults(conn)[[1]])
  #关闭连接
  dbDisconnect(conn)
  return(dat)
}

usesql <- function(sql) {
  return(criterions_cite(dbname, username, password, host, port, sql))
}

#测试马桶id
toilets <- "6A010203050E:6A010203050D:6A0102030512:6A010203050F:6A010203050C:6A010203050B" %>%
  fenge()
#公司内部手机号
phones <- "13818220833:13917777600:18917811116:18904612333:13651733693:13936550298:13601937179:18525547010:13585612110:15618975413:18774256407:13072178722:18017992993:18217506517:13673426049" %>% fenge()

sqlstr <- ""
for(i in seq_along(phones)){
  if(i != length(phones)){
  sqlstr <- paste0(sqlstr,paste0(" phone = ","'",phones[i],"'"," or"))
  }else{
    sqlstr <- paste0(sqlstr,paste0(" phone = ","'",phones[i],"'"))
  }
}
sqlstr <- paste0("select * from users where",sqlstr)

data <- usesql(sqlstr)
data





data <- usesql("select * from users")

boys <- which(data$sex == 1) %>% length()
grils <- which(data$sex == 2) %>% length()
counts <- nrow(data)

data %<>% filter(created_at > "2020-11-17 23:59:59") %>%
  filter(created_at < "2020-11-19 00:00:00")

#for (i in seq_along(phones)) {
#  data %<>% filter(phone != phones[i])
#}

newusrs <- nrow(data)

data <- usesql("select * from toilet_usage_records")
data %<>% filter(created_at > "2020-11-17 23:59:59") %>%
  filter(created_at < "2020-11-19 00:00:00")


